# -*- coding: utf-8 -*-
from db_conn import *
import io
import time
from datetime import datetime, timedelta
import traceback
import re
from anatel_utils import *
from math import fabs, radians, cos, sin, asin, sqrt

date_now = datetime.now()
in_file_name_1 = "" + date_now.strftime("%Y-%m-%d") + "_file1.txt"
in_file_name_2 = "" + date_now.strftime("%Y-%m-%d") + "_file2.txt"
export_file_id = "" + date_now.strftime("%Y-%m-%d") + "-export-db-id.txt"
export_file = "" + date_now.strftime("%Y-%m-%d") + "-export-db.txt"

style_css="""
<style type="text/css">
#table-3{
    border: 1px solid #DFDFDF;background-color: #F9F9F9;width: 100%;-moz-border-radius: 3px;-webkit-border-radius: 3px;border-radius: 3px;font-family: Arial,"Bitstream Vera Sans",Helvetica,Verdana,sans-serif;color: #333;}
#table-3 td, #table-3 th {
    border-top-color: white;border-bottom: 1px solid #DFDFDF;color: #555;}
#table-3 th {
    text-shadow: rgba(255, 255, 255, 0.796875) 0px 1px 0px;font-family: Georgia,"Times New Roman","Bitstream Charter",Times,serif;font-weight: normal;padding: 7px 7px 8px;text-align: left;line-height: 1.3em;font-size: 14px;}
#table-3 td {
    font-size: 12px;padding: 4px 7px 2px;vertical-align: top;
}</style>
"""

def float_eq(a, b, epsilon=0.00000001):
    return abs(a - b) < epsilon

def addEnlace(aid_antena,bid_antena,enlace_erro_codes):
    cursor,conn = db_connect(dict_cursor=False,ss_cursor=True)
    try:
        #insert_query="insert into anatel_enlaces values(NULL,%s,%s,NULL)" % (aid_antena,bid_antena)
        insert_query="INSERT INTO `anatel_enlaces` (`id_antena_1`, `id_antena_2`) VALUES (%s, %s);" % (aid_antena,bid_antena)
        #print "insert_query: %s" % insert_query        
        cursor.execute(insert_query)
        
        last_row_id = cursor.lastrowid
        for erro in enlace_erro_codes:
            insert_erros = "INSERT INTO `anatel_enlace_erros` (`id_enlace`, `id_error_code`) VALUES (%s, %s);" % (last_row_id,erro)
            #print "insert_erros: %s" % insert_erros            
            cursor.execute(insert_erros)

    except:
        #print "Erro ao atualizar anatel_enlaces: %s" % (insert_query)
        exceptionType, exceptionValue, exceptionTraceback = sys.exc_info()
        traceback.print_exception(exceptionType, exceptionValue, exceptionTraceback,
                          limit=2, file=sys.stdout)
        sys.exit(1)#se estiver na chain de sucesso da crontab, retorna erro e sai
    finally:
        db_disconnect(cursor,conn)                
    
def transCord(coord):
    
    dd = int(coord[0:2])
    mm = round(int(coord[3:5])/60,6)
    ss = round(float(coord[5:7] + "." + coord[7:])/3600,6)
    coord_new = dd + mm + ss
    if(coord.find("S")>-1 or coord.find("W")>-1):
        coord_new=coord_new*-1
    
    return coord_new
    
def enlaceCheck(aid_antena,bid_antena,
                aTipoPolarizacao,bTipoPolarizacao,
                aAzimute,bAzimute,
                aRede,bRede,
                aUF,bUF,
                aLatitude,bLatitude,
                aLongitude,bLongitude):
    erro = 0
    error_codes=[]
    try:
        
        #TipoPolarizacao
        if not(aTipoPolarizacao == bTipoPolarizacao):
            #TipoPolarizacao = 1
            erro=erro+1
            error_codes.append(4) #erro code 4, tipo polarizacao
            #pass
        
        #Azimute
        if aAzimute == None or bAzimute==None:
            erro=erro+1
            error_codes.append(8) #erro code 8, azimute none
        elif not( float_eq(fabs((fabs(aAzimute) - float(bAzimute))),180.0)):    
            erro_azimute = fabs(fabs((fabs(aAzimute) - float(bAzimute))) - 180.0) #se erro de azimute for menor que 10.

            if (erro_azimute<=10):
                
                #print "conta Azimute=%s" % (fabs((fabs(aAzimute) - float(bAzimute))))
                #print "aAzimute=%s" % (aAzimute)
                #print "bAzimute=%s" % (bAzimute)
                #print "erro_azimute=%s" % (erro_azimute)    
                            
                erro=erro+.5
                error_codes.append(3) #erro code 3, azimute menor que 10 graus
                #pass
            else:
                erro=erro+1
                error_codes.append(2) #erro code 2, azimute maior que 10 graus
                
        
        #UF
        if not( aUF == bUF):
            try:
                distancia = haversine(transCord(aLongitude), transCord(aLatitude), transCord(bLongitude), transCord(bLatitude))
                #print "distancia: %s" % (distancia)          
                if (distancia <= 500):
                    erro=erro+.5
                    error_codes.append(6) #erro code 6, azimute menor que 500km
                    #pass
                else:
                    erro=erro+1
                    error_codes.append(5) #erro code 5, azimute maior que 10 graus
            except:
                erro=erro+1
                error_codes.append(9) #erro de coordernada                
        
        #Rede
        if not(aRede[:-3] == bRede[:-3]):
            erro=erro+1
            error_codes.append(7) #erro code 7, erro rede
            #pass
        
    except:
        print "aid_antena: %s" % (aid_antena)
        print "bid_antena: %s" % (bid_antena)
        print "aTipoPolarizacao: %s" % (aTipoPolarizacao)
        print "bTipoPolarizacao: %s" % (bTipoPolarizacao)
        print "aAzimute: %s" % (aAzimute)
        print "bAzimute: %s" % (bAzimute)
        print "aRede: %s" % (aRede)
        print "bRede: %s" % (bRede)
        print "aUF: %s" % (aUF)
        print "bUF: %s" % (bUF)
        print "aLatitude: %s" % (aLatitude)
        print "bLatitude: %s" % (bLatitude)
        print "aLongitude: %s" % (aLongitude)
        print "bLongitude: %s" % (bLongitude)
        exceptionType, exceptionValue, exceptionTraceback = sys.exc_info()
        traceback.print_exception(exceptionType, exceptionValue, exceptionTraceback,
                          limit=2, file=sys.stdout)
        sys.exit(1)#se estiver na chain de sucesso da crontab, retorna erro e sai
    
    return bid_antena,erro,error_codes
    
def haversine(lon1, lat1, lon2, lat2):
    """
    Calculate the great circle distance between two points 
    on the earth (specified in decimal degrees)
    """
    # convert decimal degrees to radians 
    lon1, lat1, lon2, lat2 = map(radians, [lon1, lat1, lon2, lat2])
    # haversine formula 
    dlon = lon2 - lon1 
    dlat = lat2 - lat1 
    a = sin(dlat/2)**2 + cos(lat1) * cos(lat2) * sin(dlon/2)**2
    c = 2 * asin(sqrt(a)) 
    km = 6367 * c
    return km
        
def findInsertEnlaces():    
    #cursor,conn = db_connect(dict_cursor=True)
    cursor,conn = db_connect(dict_cursor=False,ss_cursor=True)
    #cursor,conn = db_connect()
    try:
        #full query que nao vai interessar mais pra frente.
        query_enlaces_na="""
        select a.id_antena,b.id_antena
        from anatel_real a, anatel_real b
        where (a.TransmissaoInicial = b.RecepcaoInicial and a.RecepcaoInicial = b.TransmissaoInicial)
        and a.NumEntidade = b.NumEntidade
        and a.TipoPolarizacao = b.TipoPolarizacao
        and a.DesignacaoEmissao = b.DesignacaoEmissao
        and ABS(a.Azimute) - b.Azimute = 180
        and concat('',RIGHT(a.Rede,1) * 1) <> RIGHT(a.Rede,1)
        and concat('',RIGHT(b.Rede,1) * 1) <> RIGHT(b.Rede,1)
        and LEFT(a.Rede,5) = LEFT(b.Rede,5)
        and a.UF = b.UF
        """
        
        query_enlaces_na="""
        select a.id_antena as aid_antena,b.id_antena as bid_antena,
        a.TipoPolarizacao  as aTipoPolarizacao,b.TipoPolarizacao as bTipoPolarizacao,
        a.Azimute as aAzimute,b.Azimute  as bAzimute,
        a.Rede as aRede,b.Rede as bRede,
        a.UF as aUF,b.UF as bUF,
        a.Latitude as aLatitude,b.Latitude as bLatitude,
        a.Longitude as aLongitude,b.Longitude as bLongitude,
        CONCAT(LEAST(a.id_antena,b.id_antena),GREATEST(a.id_antena,b.id_antena)) AS uniq_id_antena
        from anatel_real a, anatel_real b
        where (a.TransmissaoInicial = b.RecepcaoInicial and a.RecepcaoInicial = b.TransmissaoInicial)
        and a.NumEntidade = b.NumEntidade
        and a.DesignacaoEmissao = b.DesignacaoEmissao
        and RIGHT(a.Rede,2) NOT REGEXP('(^[0-9]+$)')
        and RIGHT(b.Rede,2) NOT REGEXP('(^[0-9]+$)')
        GROUP BY uniq_id_antena
        ORDER BY a.id_antena
        """
                
        cursor.execute(query_enlaces_na)
        #resp = cursor.fetchall()
        #for row in resp:
        
        dict_id_antena = {0:0} #inicializando dict pra evitar um if no loop 
        
        for row in cursor:
            
            if dict_id_antena.has_key(row[0]):
                bid_antena,errors_qty,errors_codes = enlaceCheck(row[0],row[1],
                row[2],row[3],
                row[4],row[5],
                row[6],row[7],
                row[8],row[9],
                row[10],row[11],
                row[12],row[13],
                )
                if errors_qty < dict_id_antena[row[0]][1]: #verifica se o cadastro verificado tem menos erro do que o atual no dict
                    dict_id_antena[row[0]] = [bid_antena,errors_qty,errors_codes]
            else:
                #print "Analisando cadastro %s" % (row[0])
                
                #insere o com menos erro:                
                for k,v in dict_id_antena.iteritems():
                    if (k!=0):#verifica se eh mesmo o retorno de enlacesCheck                        
                        addEnlace(k,v[0],v[2])
                        print "Fim da analise cadastro %s , Qtd erros: %s" % (k,v[1])
                    
                dict_id_antena = {} #zera dict
                dict_id_antena[row[0]]=[]
                bid_antena,errors_qty,errors_codes = enlaceCheck(row[0],row[1],
                row[2],row[3],
                row[4],row[5],
                row[6],row[7],
                row[8],row[9],
                row[10],row[11],
                row[12],row[13],
                )
                dict_id_antena[row[0]]=[bid_antena,errors_qty,errors_codes]
                                     
            
            '''
            enlaceCheck(row['aid_antena'],row['bid_antena'],
                row['aTipoPolarizacao'],row['bTipoPolarizacao'],
                row['aAzimute'],row['bAzimute'],
                row['aRede'],row['bRede'],
                row['aUF'],row['bUF'],
                row['aLatitude'],row['bLatitude'],
                row['aLongitude'],row['bLongitude'])
            '''

                       
            #cursor.execute(insert_query)
    except:
        #print "Erro ao atualizar anatel_enlaces: %s" % (insert_query)
        exceptionType, exceptionValue, exceptionTraceback = sys.exc_info()
        traceback.print_exception(exceptionType, exceptionValue, exceptionTraceback,
                          limit=2, file=sys.stdout)
    finally:
        
        db_disconnect(cursor,conn)        
        
    
  
if __name__ == '__main__':
    if not len(sys.argv) < 2:
        
        for v in sys.argv:
            if len(v.split("="))>1:
                param = v.split("=")[0].strip()
                valor = v.split("=")[1].strip()
                try:
                    if param.lower().strip().find("days_offset")>-1:
                        days_offset = valor
                        print "days_offset: %s" % (days_offset)                                           
                    else:
                        eval('%s=%s' % (param,valor))   
                    #print "Param: %s" % (param)
                    #print "Valor: %s" % (valor)
                except:
                    exceptionType, exceptionValue, exceptionTraceback = sys.exc_info()
                    traceback.print_exception(exceptionType, exceptionValue, exceptionTraceback,
                                      limit=2, file=sys.stdout)      
    start_total = time.time()
    
    print "Comecando" % ()

    findInsertEnlaces()

    print "(Total Tempo Gasto para Enlaces: %s)" % ((time.time() - start_total))